package com.bankLogisticsSys.service;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class ManageExpressService {
	
	public final static int WHOLE_SITUATION = 10;
	public final static int EMS = 1;

	public String sql = "select id,if(flag=0,'下行','上行')flag,CAST(trackingID AS CHAR)trackingID,"
			+ "(select network from networkinfo where id = expressinfo.source) as source, "
			+ "(select business from business where businessID = expressinfo.businessID) as business, "
			+ "(select network from networkinfo where id = expressinfo.destination) as destination,"
			+ "(select bankType from banktype where id = expressinfo.bank) as bank, "
			+ "sendDate, receiveDate,"
			+ "if(state=0,'已验收',if(state=2,'退回银行','运输中'))state,"
			+ "if(abnormal=0,'正常',if(abnormal=1,'操作异常',if(abnormal=2,'线路异常',if(abnormal=3,'超时','正常'))))abnormal "
			+ "from expressinfo where sendDate = ? ";
	
	public List<Record> getExpressList(Date date, int page, int rows, int flag, int state, int abnormal, int bank){
		int start = ((page - 1) * rows);
		int end = (page * rows);
		String condition="";
		List<Object> para = new ArrayList<>();
		para.add(date);
		if(bank !=EMS){
			condition=" and bank = ?";
			para.add(bank);
		}
		if(flag !=WHOLE_SITUATION){
			condition=" and flag = ?";
			para.add(flag);
		}
		if(state !=WHOLE_SITUATION){
			condition+=" and state = ?";
			para.add(state);
		}
		if(abnormal != WHOLE_SITUATION){
			condition +=" and abnormal = ?";
			para.add(abnormal);
		}
		para.add(start);
		para.add(end);
		String str = sql
				+ condition + " order by bank limit ?,?";
		return Db.find(str, para.toArray());
	}
	
	public long getCount(Date date){
		return Db.queryLong("select count(*) from expressinfo where sendDate = ?",date);
	}
	
	public List<Record> getExpressList(Date date, int flag, int state, int abnormal){
		List<Record> list = Db.find(sql,date);
		return list;
	}
}
